#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL数据库操作演示
展示韶华墨韵项目的数据库操作能力
"""

import mysql.connector
from mysql.connector import Error
import sys
from datetime import datetime

# 数据库连接配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'database': 'shufa',
    'user': 'root',
    'password': 'root',
    'charset': 'utf8mb4',
    'use_unicode': True,
    'autocommit': False
}

def connect_database():
    """
    连接数据库
    """
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        print(f"✅ 成功连接到数据库: {DB_CONFIG['database']}")
        return connection
    except Error as e:
        print(f"❌ 数据库连接失败: {e}")
        return None

def show_database_info(connection):
    """
    显示数据库基本信息
    """
    cursor = connection.cursor(dictionary=True)
    
    print("\n📊 数据库基本信息:")
    
    # MySQL版本
    cursor.execute("SELECT VERSION() as version")
    version = cursor.fetchone()['version']
    print(f"  MySQL版本: {version}")
    
    # 当前数据库
    cursor.execute("SELECT DATABASE() as db_name")
    db_name = cursor.fetchone()['db_name']
    print(f"  当前数据库: {db_name}")
    
    # 表数量
    cursor.execute("SHOW TABLES")
    tables = cursor.fetchall()
    print(f"  表数量: {len(tables)}")
    
    cursor.close()

def show_system_tables(connection):
    """
    显示若依系统核心表信息
    """
    cursor = connection.cursor(dictionary=True)
    
    print("\n🔍 若依系统核心表:")
    
    system_tables = {
        'sys_user': '用户表',
        'sys_role': '角色表',
        'sys_menu': '菜单表',
        'sys_dept': '部门表',
        'sys_config': '系统配置表'
    }
    
    for table_name, description in system_tables.items():
        try:
            cursor.execute(f"SELECT COUNT(*) as count FROM {table_name}")
            count = cursor.fetchone()['count']
            print(f"  ✅ {description} ({table_name}): {count} 条记录")
        except Error as e:
            print(f"  ❌ {description} ({table_name}): 表不存在")
    
    cursor.close()

def show_business_tables(connection):
    """
    显示业务表信息
    """
    cursor = connection.cursor(dictionary=True)
    
    print("\n📋 业务表信息:")
    
    business_tables = {
        'works': '书法作品表',
        'competition': '比赛表',
        'comment': '评论表',
        'user_profile': '用户资料表'
    }
    
    for table_name, description in business_tables.items():
        try:
            cursor.execute(f"SELECT COUNT(*) as count FROM {table_name}")
            count = cursor.fetchone()['count']
            print(f"  ✅ {description} ({table_name}): {count} 条记录")
        except Error as e:
            print(f"  ⚠️  {description} ({table_name}): 表不存在或未创建")
    
    cursor.close()

def show_sample_users(connection):
    """
    显示用户表示例数据
    """
    cursor = connection.cursor(dictionary=True)
    
    print("\n👥 用户表示例数据:")
    
    try:
        cursor.execute("""
            SELECT user_id, user_name, nick_name, email, status, create_time 
            FROM sys_user 
            WHERE del_flag = '0' 
            ORDER BY create_time DESC 
            LIMIT 5
        """)
        
        users = cursor.fetchall()
        
        if users:
            print(f"  共找到 {len(users)} 个用户 (显示最新5个):")
            print(f"  {'ID':<8} {'用户名':<15} {'昵称':<15} {'邮箱':<25} {'状态':<8} {'创建时间':<20}")
            print("  " + "-" * 95)
            
            for user in users:
                status_text = "正常" if user['status'] == '0' else "停用"
                create_time = user['create_time'].strftime('%Y-%m-%d %H:%M') if user['create_time'] else 'N/A'
                print(f"  {user['user_id']:<8} {user['user_name']:<15} {user['nick_name']:<15} {user['email'] or 'N/A':<25} {status_text:<8} {create_time:<20}")
        else:
            print("  暂无用户数据")
            
    except Error as e:
        print(f"  ❌ 查询用户数据失败: {e}")
    
    cursor.close()

def show_menu_structure(connection):
    """
    显示菜单结构
    """
    cursor = connection.cursor(dictionary=True)
    
    print("\n🗂️  系统菜单结构:")
    
    try:
        cursor.execute("""
            SELECT menu_id, menu_name, parent_id, order_num, menu_type, visible
            FROM sys_menu 
            WHERE parent_id = 0 AND status = '0'
            ORDER BY order_num
        """)
        
        top_menus = cursor.fetchall()
        
        if top_menus:
            print(f"  顶级菜单 (共 {len(top_menus)} 个):")
            for menu in top_menus:
                menu_type_text = {'M': '目录', 'C': '菜单', 'F': '按钮'}.get(menu['menu_type'], '未知')
                visible_text = "显示" if menu['visible'] == '0' else "隐藏"
                print(f"    📁 {menu['menu_name']} (ID:{menu['menu_id']}, 类型:{menu_type_text}, {visible_text})")
                
                # 显示子菜单
                cursor.execute("""
                    SELECT menu_id, menu_name, menu_type, visible
                    FROM sys_menu 
                    WHERE parent_id = %s AND status = '0'
                    ORDER BY order_num
                    LIMIT 3
                """, (menu['menu_id'],))
                
                sub_menus = cursor.fetchall()
                for sub_menu in sub_menus:
                    sub_type_text = {'M': '目录', 'C': '菜单', 'F': '按钮'}.get(sub_menu['menu_type'], '未知')
                    print(f"      └─ {sub_menu['menu_name']} ({sub_type_text})")
                
                if len(sub_menus) >= 3:
                    cursor.execute("""
                        SELECT COUNT(*) as count
                        FROM sys_menu 
                        WHERE parent_id = %s AND status = '0'
                    """, (menu['menu_id'],))
                    total_sub = cursor.fetchone()['count']
                    if total_sub > 3:
                        print(f"      └─ ... 还有 {total_sub - 3} 个子菜单")
        else:
            print("  暂无菜单数据")
            
    except Error as e:
        print(f"  ❌ 查询菜单数据失败: {e}")
    
    cursor.close()

def test_database_operations(connection):
    """
    测试数据库操作权限
    """
    cursor = connection.cursor(dictionary=True)
    
    print("\n🧪 数据库操作测试:")
    
    try:
        # 创建临时测试表
        cursor.execute("""
            CREATE TEMPORARY TABLE test_operations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                test_data VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("  ✅ 创建临时表: 成功")
        
        # 插入测试数据
        test_data = f"测试数据 - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        cursor.execute(
            "INSERT INTO test_operations (test_data) VALUES (%s)",
            (test_data,)
        )
        print("  ✅ 插入数据: 成功")
        
        # 查询测试数据
        cursor.execute("SELECT * FROM test_operations")
        result = cursor.fetchone()
        if result:
            print(f"  ✅ 查询数据: 成功 - {result['test_data']}")
        
        # 更新测试数据
        cursor.execute(
            "UPDATE test_operations SET test_data = %s WHERE id = %s",
            (f"更新后的数据 - {datetime.now().strftime('%H:%M:%S')}", result['id'])
        )
        print("  ✅ 更新数据: 成功")
        
        # 删除测试数据
        cursor.execute("DELETE FROM test_operations WHERE id = %s", (result['id'],))
        print("  ✅ 删除数据: 成功")
        
        # 提交事务
        connection.commit()
        print("  ✅ 事务提交: 成功")
        
    except Error as e:
        print(f"  ❌ 操作测试失败: {e}")
        connection.rollback()
    
    cursor.close()

def main():
    """
    主函数
    """
    print("="*60)
    print("韶华墨韵项目 - MySQL数据库操作演示")
    print("="*60)
    
    # 连接数据库
    connection = connect_database()
    if not connection:
        return
    
    try:
        # 显示数据库基本信息
        show_database_info(connection)
        
        # 显示系统表信息
        show_system_tables(connection)
        
        # 显示业务表信息
        show_business_tables(connection)
        
        # 显示用户示例数据
        show_sample_users(connection)
        
        # 显示菜单结构
        show_menu_structure(connection)
        
        # 测试数据库操作
        test_database_operations(connection)
        
        print("\n" + "="*60)
        print("🎉 数据库操作演示完成!")
        print("\n💡 可用的操作包括:")
        print("  - 查询系统用户、角色、菜单等基础数据")
        print("  - 管理书法作品、比赛、评论等业务数据")
        print("  - 执行增删改查等完整的CRUD操作")
        print("  - 事务管理和数据备份")
        print("="*60)
        
    except Exception as e:
        print(f"\n❌ 演示过程中发生错误: {e}")
    
    finally:
        if connection and connection.is_connected():
            connection.close()
            print("\n🔌 数据库连接已关闭")

if __name__ == "__main__":
    main()